package WorldCup2010.DAO;

import java.sql.*;
import java.util.*;
import WorldCup2010.VO.*;

public class Match {
	private ResultSet rs;
	
	public ArrayList<InfoMatch> getAllMatches()
	{
		ArrayList<InfoMatch> list = new ArrayList<InfoMatch>();
		
		ConnectionFactory connectionFactory = ConnectionFactory.getDefaultFactory();
		connectionFactory.createConnection();
		
		String sql = "select match_day, match_no, home.nation_name as home , " +
				"away.nation_name as away  from match, team home , team away " +
				"where (match.home_team = home.team_no AND match.away_team = away.team_no) order by match_day";			
			
		rs = connectionFactory.executeQuery(sql);
			
		try {
			while(rs.next())
			{
				InfoMatch mInfoMatch = new InfoMatch();
				mInfoMatch.setMatch_day(rs.getString("match_day")); 
				mInfoMatch.setMatch_no(rs.getString("match_no"));
				mInfoMatch.setHome_team(rs.getString("home")); 
				mInfoMatch.setAway_team(rs.getString("away"));
				
				list.add(mInfoMatch);	
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
	
		connectionFactory.closeConnection();
		return list;
	}	
	
	public InfoMatch getMatches(String matchNo)
	{
		InfoMatch mInfoMatch = new InfoMatch();
		ConnectionFactory connectionFactory = ConnectionFactory.getDefaultFactory();
		connectionFactory.createConnection();
	
		String sql = "select match_no,is_matched,match_day,audience,referee,weather,home.nation_name as home , " +
				"away.nation_name as away, temperature,stadium_name from match, team home , team away " +
				"where match_no = '" + matchNo +
				"' AND (match.home_team = home.team_no AND match.away_team = away.team_no)";			
		rs = connectionFactory.executeQuery(sql);
		
		try {
			rs.next();
			mInfoMatch.setMatch_no(rs.getString("match_no"));
			mInfoMatch.setIsMatched(rs.getString("is_matched"));
			mInfoMatch.setMatch_day(rs.getString("match_day")); 
			mInfoMatch.setAudience(rs.getLong("audience"));
			mInfoMatch.setRefree(rs.getString("referee"));
			mInfoMatch.setWeather(rs.getString("weather"));
			mInfoMatch.setHome_team(rs.getString("home")); 
			mInfoMatch.setAway_team(rs.getString("away"));
			mInfoMatch.setTemperature(rs.getInt("temperature"));
			mInfoMatch.setStadiumName(rs.getString("stadium_name"));
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		
		return mInfoMatch;
	}	
	public ArrayList<InfoMatchPlayer> getMatchPlayer(String matchNo, String teamName)
	{
		ArrayList<InfoMatchPlayer> list = new ArrayList<InfoMatchPlayer>();
		
		ConnectionFactory connectionFactory = ConnectionFactory.getDefaultFactory();
		connectionFactory.createConnection();
		
		String sql = "select player_match_result.player_no, player.player_name, player_match_result.match_no, goal, assist, yellow_card, "
			+ " red_card, shotting, shotting_valid, pass, pass_succeed, "
			+ " tackle, tackle_succeed, heading, heading_succeed "
			+ " from player_match_result, player, team " 
			+ " where match_no ='"
			+ matchNo + "' AND " 
			+ " (player_match_result.player_no = player.player_no AND team.nation_name = '"
			+ teamName + "' AND team.team_no = player.team_no)";
		
		rs = connectionFactory.executeQuery(sql);
			
		try {
			while(rs.next())
			{
				InfoMatchPlayer mInfoMatchPlayer = new InfoMatchPlayer();
				mInfoMatchPlayer.setPlayerNo(rs.getString("player_no")); 
				mInfoMatchPlayer.setMatchNo(rs.getString("match_no"));
				mInfoMatchPlayer.setPlayerName(rs.getString("player_name"));
				mInfoMatchPlayer.setGoals(rs.getInt("goal")); 
				mInfoMatchPlayer.setAssists(rs.getInt("assist"));
				mInfoMatchPlayer.setYellowCard(rs.getInt("yellow_card")); 
				mInfoMatchPlayer.setRedCard(rs.getInt("red_card"));
				mInfoMatchPlayer.setShotting(rs.getInt("shotting")); 
				mInfoMatchPlayer.setShottingValid(rs.getInt("shotting_valid"));
				mInfoMatchPlayer.setPass(rs.getInt("pass")); 
				mInfoMatchPlayer.setPassSucceed(rs.getInt("pass_succeed"));
				mInfoMatchPlayer.setTackle(rs.getInt("tackle")); 
				mInfoMatchPlayer.setTackleSucced(rs.getInt("tackle_succeed"));
				mInfoMatchPlayer.setHeading(rs.getInt("heading")); 
				mInfoMatchPlayer.setHeadingSucced(rs.getInt("heading_succeed"));
				
				list.add(mInfoMatchPlayer);	
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
	
		connectionFactory.closeConnection();
		return list;
	}
}
